package com.emser.unc.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

	public static final String TABLE_CUSTOMER = "customer";
	public static final String TABLE_PRODUCT = "product";
	public static final String TABLE_VERSION = "version";
	public static final String TABLE_SALE_ORDER = "sale_order";
	public static final String TABLE_SALE_REPORT = "sale_report";
	public static final String TABLE_NO_SALE_REPORT = "no_sale_report";	
	
	public static final String TABLE_CUSTOMER_SEND_PENDING = "customer_send_pending";
	public static final String TABLE_NO_SALE_REPORT_PENDING = "no_sale_report_pending";
	public static final String TABLE_SALE_REPORT_PENDING = "sale_report_pending";
	public static final String TABLE_SALE_REPORT_SENT = "sale_report_sent";
	
	public static final String COLUMN_ID = "_id";
	private static final int DATABASE_VERSION = 1;

	public MySQLiteHelper(Context context) {
		super(context, "cloudmobile.db", null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {

		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_VERSION
				+ "("
				+ BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, table_name VARCHAR, last_version BIGINT)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_CUSTOMER
				+ "("
				+ BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, customer_id BIGINT, first_name VARCHAR, last_name VARCHAR, "
				+ "address VARCHAR, telephone VARCHAR)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_PRODUCT
				+ "("
				+ BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, product_id BIGINT, title VARCHAR, unit_price REAL, "
				+ "stock BIGINT)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_SALE_ORDER
				+ "("
				+ BaseColumns._ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, product_id BIGINT, quantity BIGINT, total_price REAL, sale_report_id INTEGER)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_SALE_REPORT
				+ "(sale_report_id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id BIGINT, seller_username VARCHAR, amount REAL)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_NO_SALE_REPORT
				+ "(no_sale_report_id INTEGER PRIMARY KEY AUTOINCREMENT, code BIGINT, seller_username VARCHAR, customer_id BIGINT)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_CUSTOMER_SEND_PENDING
				+ "(pending_id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id BIGINT)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_NO_SALE_REPORT_PENDING
				+ "(pending_id INTEGER PRIMARY KEY AUTOINCREMENT, no_sale_report_id INTEGER)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_SALE_REPORT_PENDING
				+ "(pending_id INTEGER PRIMARY KEY AUTOINCREMENT, sale_report_id INTEGER)");
		database.execSQL("CREATE TABLE IF NOT EXISTS "
				+ TABLE_SALE_REPORT_SENT
				+ "(sale_id INTEGER PRIMARY KEY AUTOINCREMENT, sale_report_id INTEGER)");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(MySQLiteHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS customer");
		db.execSQL("DROP TABLE IF EXISTS product");
		db.execSQL("DROP TABLE IF EXISTS version");
		onCreate(db);
	}

}